<?php
/**
 * Created by PhpStorm.
 * User: xeng
 * Date: 2018/1/20
 * Time: 17:20
 * 数据统计 类
 */
namespace Home\Controller;
use Think\Controller;
class StatsController extends AuthController
{
    public function index()
    {
        
        $this->display('mail');
    }

    /*
     * 留存统计
     */
    public function keep(){
        // var_dump(cal_days_in_month(CAL_GREGORIAN,5,2018));
        $this->CheckPower('6001'); 
        $r=$this->actionLog('全部','查询了','留存统计');
        $condi=$_GET;
        $condi=array_merge($condi,$_POST);
        $limit = (int)10;
        $p=$condi['p']?(int)$condi['p']-1:(int)0;
        $skip=$p*$limit;
        $p=$condi['p']?(int)$condi['p']:(int)1;
        $where =' 1=1 ';
        if($condi['start_time']){
            $where .= ' and time >= "'.$condi['start_time'].' 00:00:00"';

        } 
        if($condi['end_time']){
            $where .= ' and time <= "'.$condi['end_time'].' 23:59:59"';
        } 
        // 1 查询留存数据库  今日数据是否有
        $today=date("Y-m-d",time());
        $todayLiuCun=$this->weblog("SELECT id FROM data_liucun WHERE time='$today'");  
        if($todayLiuCun){  //今日之前数据已经存入  只需修改七日留存为空的数据
            if($p==1){
                $sevenLiuCun=$this->weblog('SELECT time FROM data_liucun WHERE d6 = \'0%\' ORDER by time ');
                $sevenTime=substr($sevenLiuCun[0]['time'],0,10);
                $ToWhere ='';
                $ToWhere .= '  (time_virtual between "'.$sevenTime.' 00:00:00"  and  "'.$today.' 23:59:59") ';
                $Tolist=$this->gamelog("SELECT m.time as timea,count(m.id) as login_count  
                                    FROM (SELECT id,SUBSTR(time_virtual,1,10) as time FROM game_loginout WHERE type_virtual=1 and $ToWhere 
                                    GROUP BY SUBSTR(time_virtual,1,10),JSON_EXTRACT(log, '$.account') ORDER BY time_virtual DESC ) m  
                                    GROUP BY m.time");
                foreach ($Tolist as $key => $value) {
                     $this->regpoe($value['timea'],$value['login_count']);
                }  
            }
           
            ///统计好的数据
            $result=$this->weblog("SELECT *,SUBSTR(time,1,10)  as timea  FROM data_liucun WHERE $where  ORDER by time desc limit $skip,$limit");
            $count=$this->weblog("SELECT id FROM data_liucun where $where");
            
            $page_show = $this->page_show(count($count),$limit,$p);
            $shows=$page_show;
            $pg=array_pop($shows);
            $this->assign("totalPages",$pg);
            $this->assign("p",$p);
            $this->assign("condi",$condi);
            $this->assign("count",count($count));
            $this->assign('lists',$result);
            $this->display();
            die();
        }else{  ///今日不存在  需要填入最后一条数据 到今日的数据
            $OtherDay=$this->weblog("SELECT time FROM data_liucun   ORDER by time DESC ");
            $OtherTime=substr($OtherDay[0]['time'],0,10); 
            $YesTeDay= date("Y-m-d",strtotime('+1 days',strtotime(''.$OtherTime.'')));    ////获取最后一个时间的第二天时间
            $ToWhere ='';
            $ToWhere .= '  (time_virtual between "'.$YesTeDay.' 00:00:00"  and  "'.$today.' 23:59:59") ';
            $Tolist=$this->gamelog("SELECT m.time as timea,count(m.id) as login_count  
                                FROM (SELECT id,SUBSTR(time_virtual,1,10) as time FROM game_loginout WHERE type_virtual=1 and  $ToWhere 
                                GROUP BY SUBSTR(time_virtual,1,10),JSON_EXTRACT(log, '$.account') ORDER BY time_virtual DESC ) m  
                                GROUP BY m.time");
            foreach ($Tolist as $key => $value) {
                $insert=$this->weblog("INSERT INTO data_liucun (time,login_peo) VALUES ('".$value['timea']."',".$value['login_count'].")");
                $this->regpoe($value['timea'],$value['login_count']);
            } 

            $result=$this->weblog("SELECT *,SUBSTR(time,1,10)  as timea  FROM data_liucun ORDER by time desc limit $skip,$limit");
            $count=$this->weblog("SELECT id FROM data_liucun");
            $page_show = $this->page_show(count($count),$limit,$p);
            $shows=$page_show;
            $pg=array_pop($shows);
            $p=$condi['p']?(int)$condi['p']:(int)1;
            $this->assign("totalPages",$pg);
            $this->assign("p",$p);
            $this->assign("count",count($count));
            $this->assign('lists',$result);
            $this->display();
            die();
        }
    }   

    //查询注册 统计
    public  function regpoe($time,$login_count){
        // $time=$_GET['atime'];
        if($time){
              $where .= '  (time_virtual between "'.$time.' 00:00:00"  and  "'.$time.' 23:59:59") ';
        } 
        $listsm=$this->gamelog("SELECT  count(m.id) as reg_count  
                                FROM (SELECT id,SUBSTR(time_virtual,1,10) as time FROM game_loginout WHERE type_virtual=4 and  $where
                                GROUP BY SUBSTR(time_virtual,1,10),JSON_EXTRACT(log, '$.account') ) m  
                                GROUP BY m.time");
        $timeas=$time;
        $reg_count=$listsm[0]['reg_count']?$listsm[0]['reg_count']:0;
        $result=$this->GetKeepHistory($time,$listsm[0]['reg_count']);
        $rows=$this->weblog('UPDATE data_liucun set login_peo='.$login_count.',reg_count='.$reg_count.',d1="'.$result['d1'].'",
                            d2="'.$result['d2'].'",d3="'.$result['d3'].'",d4="'.$result['d4'].'",d5="'.$result['d5'].'",d6="'.$result['d6'].'" WHERE time="'.$timeas.' 00:00:00"');
    }


    function GetKeepHistory($day,$reg_count){
        $time[] = Date("Y-m-d H:i:s", strtotime($day));        //今天   
        $time[] = Date("Y-m-d",strtotime($day)+(1*24*60*60) ); //  次日 留存
        $time[] = Date("Y-m-d",strtotime($day)+(2*24*60*60) ); //  3日留存
        $time[] = Date("Y-m-d",strtotime($day)+(3*24*60*60) ); // 第4天     
        $time[] = Date("Y-m-d",strtotime($day)+(4*24*60*60) ); // 第5天     
        $time[] = Date("Y-m-d",strtotime($day)+(5*24*60*60) ); // 第6天    
        $time[] = Date("Y-m-d",strtotime($day)+(6*24*60*60) ); // 第7天     
        //查询当天注册的用户  
        $user_sets=[];
        $onusers=$this->gamelog("SELECT JSON_EXTRACT(log,'$.gameid') as gameid from game_loginout
                                 WHERE SUBSTR(time_virtual,1,10) ='".$day."' 
                                 and  type_virtual =4 GROUP BY JSON_EXTRACT(log,'$.gameid')");
        $_onusers=[];
        foreach ($onusers as  $value) {
            $_onusers[]=$value['gameid'];
        }

        $user_sets[]=$_onusers;
        //查询各个留存时间   登陆的用户
        for($i=1;$i<count($time);$i++){
            $users =  $this->gamelog("SELECT JSON_EXTRACT(log,'$.gameid') as gameid from game_loginout
                                 WHERE SUBSTR(time_virtual,1,10) ='".$time[$i]."' 
                                 and  type_virtual =1 GROUP BY JSON_EXTRACT(log,'$.gameid')");
            
            $_users=[];
            foreach ($users as  $value) {
                $_users[]=$value['gameid'];
            }
            $set1=$_users;   ///登陆
            $set2=$user_sets[0];   /// 注册
            $set=array_intersect($set1,$set2);
            $successRate = floor(((int)count($set)/(int)$reg_count)*10000)/10000*100;
            $results = $successRate.'%';
            $result['d'.$i]= $results;
        }
        return $result;
    }

    
 

    /*
    * 游戏   统计
    */
    public function brnn(){
        $this->runtime();
        $this->CheckPower('5001'); 
        $table="game_server_login";
        $condi=$_GET;
        $condi=array_merge($condi,$_POST);
        //操作日志
        if(isset($condi['gametype'])){
            $tf= $this->GetAllGameType(); 
            foreach ($tf as $va) {
                if($condi['gametype']==$va[1]){
                    $ts=$va[0];
                    break;
                }
            }
        }else{ 
            $ts='全部';
        }
        $r=$this->actionLog($ts,'查询了游戏类型为','数据统计',$condi['time']);
        $nowtdate=date("Y-m-d");
        if($condi['time']<$nowtdate && $condi['time']){    ////昨天的前的数据  缓存在mysql里面

            $seatime=$condi['time'];
            $result=  $this->weblog("SELECT * from game_statistics WHERE time='$seatime'");
            //$result2=  $this->weblog("SELECT * from game_statistics WHERE time='$seatime'");
            if(!empty($result)){
                echo "=";
                $where ='';
                if($condi['gametype'] !=-1){
                    $ids= $this->GetGameServerIDs($condi['gametype']);
                    $serverIDs=implode(',',$ids);
                    $where .=" and severID in ($serverIDs)";
                }
                $result=  $this->weblog(" SELECT * from game_statistics WHERE time='$seatime' $where");
                $severid='';
                $sum_amount=$sum_bet=$sum_tax=$sum_kj="";
                foreach ($result as $key => $value) {
                    if(empty($value['bet']) && empty($value['amount'])  && empty($value['status'])){
                        $severid .=','.$value['severID'];
                    }else{
                        $sum_amount +=$value['amount'];
                        $sum_bet +=$value['bet'];
                        $sum_tax +=$value['tax'];
                        $sum_kj +=$value['bet'] + $value['amount'];
                        $result[$key]['kj']=$value['bet'] - $value['amount'];   //总开奖金额
                    }
                }
                if($severid != ''){
                    $r = $this->get_one($seatime,$severid);
                    foreach ($result as $key => $value) {
                        foreach ($r as $keys => $vo) {
                            if($value['severID'] == $vo['serverid_virtual']){
                                $result[$key]['amount']=0-$vo['amount'];  //总盈亏
                                $result[$key]['bet']=$vo['bet'];  //总下注金额
                                $result[$key]['tax']=$vo['tax'];  //总税收
                                $result[$key]['kj']=$vo['bet'] - $vo['amount'];   //总开奖金额
                                $sum_amount +=$vo['amount'];
                                $sum_bet +=$vo['bet'];
                                $sum_tax +=$vo['tax'];
                                $sum_kj +=$result[$key]['kj'];
                                $ID=$value['severID'];
                                $mysql=$this->weblog("UPDATE game_statistics set bet=".$vo['bet'].",tax=".$vo['tax'].",amount=".$vo['amount'].",status=1 WHERE severID=$ID and time=\'$seatime\'");
                                echo var_dump($mysql)."+";
                                // echo "UPDATE game_statistics set bet=".$vo['bet'].",tax=".$vo['tax'].",amount=".$vo['amount'].",status=1 WHERE severID=$ID and time='$seatime'";
                                continue;
                            }
                        }
                    }
                }


               /* echo '<pre>';
                print_r($result);
                echo '</pre>';*/
                $tinfo= $this->GetAllGameType();
                if(isset($condi['gametype'])){
                    foreach ($tinfo as $value) {
                        if($condi['gametype']==$value[1]){
                            $condi['gametype_name']=$value[0];
                            break;
                        }
                    }
                }else{
                    $condi['gametype']=-1;
                    $condi['gametype_name']="全部";
                }
                 $sum_amount = 0-$sum_amount;
                 $this->assign("sum_amount",$sum_amount); //总盈亏
                 $this->assign("sum_bet",$sum_bet);  //总下注金额
                 $this->assign("sum_tax",$sum_tax);  //总税收
                 $this->assign("sum_kj",$sum_kj);  //总开奖金额
                 $this->assign("ct",$tinfo);
                 $this->assign("condi",$condi);
                 $this->assign("lists",$result);
                 echo $this->runtime(1);
                 $this->display('mysql_game_brnn');
                 die();
            }else{
                echo "+";
                $where=' 1=1 ';
                $msyq=$this->weblog("DELETE FROM  game_statistics  where time='$seatime'");
                //条件
                $tinfo= $this->GetAllGameType();
                if(isset($condi['gametype'])){
                    foreach ($tinfo as $value) {
                        if($condi['gametype']==$value[1]){
                            $condi['gametype_name']=$value[0];
                            break;
                        }
                    }
                    if($condi['gametype']!=-1){
                        $ids= $this->GetGameServerIDs($condi['gametype']);
                        $serverIDs=implode(',',$ids);
                        $where .=" and game_server_virtual in ($serverIDs)";
                    }
                }else{
                    $condi['gametype']=-1;
                    $condi['gametype_name']="全部";
                }
                $where .=" and JSON_EXTRACT(log, '$.type') = 'login' ";
               // $nid=' (1,5,9,29,33,37,61,65,67) ';           //// 试玩场
               // $where .=" and game_server_virtual not in $nid";
                if($condi['time']){
                      $where .= ' and (time_virtual between "'.$condi['time'].' 00:00:00"  and  "'.$condi['time'].' 23:59:59") ';
                }else{
                      $where .= ' and (time_virtual between "'.date("Y-m-d",time()).' 00:00:00"  and  "'.date("Y-m-d",time()).' 23:59:59") ';
                } 
                $lists=$this->gamelog("SELECT id,time_virtual,game_server_virtual, sum(m.countid) as login_count,COUNT(id) as login 
                                        from (SELECT *,count(id) as countid FROM game_server_login where $where GROUP BY game_server_virtual,account_virtual) m
                                        group by m.game_server_virtual");

                $severid='';
                foreach ($lists as $key => $value) {
                    $lists[$key]['game'] = $this->GetGameNameByID($value['game_server_virtual']);
                    $lists[$key]['renshu']=$value['login'];//每天的登录人数 
                    $lists[$key]['cishu']=$value['login_count'];//每天的登录次数
                    $lists[$key]['time']=substr($value['time_virtual'],0,10) ;//日期
                    $severid .=','.$value['game_server_virtual'];
                    $severID=$value['game_server_virtual'];
                    $severName=$this->GetGameNameByID($value['game_server_virtual']);
                    $time=substr($value['time_virtual'],0,10);
                    $log_peo=$value['login'];
                    $log_count=$value['login_count'];
                    $insert=$this->weblog("INSERT INTO game_statistics (severName,severID,time,log_peo,log_count) VALUES ('$severName',$severID,'$time',$log_peo,$log_count)");
                }
            }
        }else{
                $where=' 1=1 ';
                $tinfo= $this->GetAllGameType();


                if(isset($condi['gametype'])){
                    foreach ($tinfo as $value) {
                        if($condi['gametype']==$value[1]){
                            $condi['gametype_name']=$value[0];
                            break;
                        }
                    }
                    if($condi['gametype']!=-1){
                        $ids= $this->GetGameServerIDs($condi['gametype']);
                        $serverIDs=implode(',',$ids);
                        $where .=" and game_server_virtual in ($serverIDs)";
                    }
                }else{
                    $condi['gametype']=-1;
                    $condi['gametype_name']="全部";
                }
                $where .=" and JSON_EXTRACT(log, \"$.type\") = \"login\" ";
                // $nid=' (1,5,9,29,33,37,61,65,67) ';            //// 试玩场
                // $where .=" and game_server_virtual not in $nid";
               
                if($condi['time']){
                      $where .= ' and (time_virtual between "'.$condi['time'].' 00:00:00"  and  "'.$condi['time'].' 23:59:59") ';
                }else{
                      $where .= ' and (time_virtual between "'.date("Y-m-d",time()).' 00:00:00"  and  "'.date("Y-m-d",time()).' 23:59:59") ';
                } 

               /* $lists=$this->gamelog("SELECT id,time_virtual,game_server_virtual, sum(m.countid) as login_count,COUNT(id) as login
                                        from (SELECT *,count(id) as countid FROM game_server_login where $where GROUP BY game_server_virtual,account_virtual) m
                                        group by m.game_server_virtual");*/

                $lists=$this->gamelog("CALL pro_add('id,time_virtual,game_server_virtual, sum(m.countid) as login_count,COUNT(id) as login','$where')");
               
                $severid='';

                foreach ($lists as $key => $value) {
                    $lists[$key]['game'] = $this->GetGameNameByID($value['game_server_virtual']);
                    $lists[$key]['renshu']=$value['login'];//每天的登录人数
                    $lists[$key]['cishu']=$value['login_count'];//每天的登录次数
                    $severid .=','.$value['game_server_virtual'];
                    $lists[$key]['time']=substr($value['time_virtual'],0,10) ;//日期
                }
            $tt = $this->get_one($lists[0]['time'],$severid);
            $sum_amount=$sum_bet=$sum_tax=$sum_kj="";
            foreach ($lists as $key => $value) {
                foreach ($tt as $keys => $vo) {
                    if($value['game_server_virtual'] == $vo['serverid_virtual']){
                        $lists[$key]['amount']=0-$vo['amount'];  //总盈亏
                        $lists[$key]['bet']=$vo['bet'];  //总下注金额
                        $lists[$key]['tax']=$vo['tax'];  //总税收
                        $lists[$key]['kj']=$vo['bet'] - $vo['amount'];   //总开奖金额
                        $sum_amount +=$vo['amount'];
                        $sum_bet +=$vo['bet'];
                        $sum_tax +=$vo['tax'];
                        $sum_kj +=$lists[$key]['kj'];
                        continue;
                    }
                }
            }
        }
        echo $this->runtime(1);


        $list['times'] = $lists[0]['time'];
        $list['serid'] = trim($severid,',');
        $sum_amount = 0-$sum_amount;
        $this->assign("sum_amount",$sum_amount); //总盈亏
        $this->assign("sum_bet",$sum_bet);  //总下注金额
        $this->assign("sum_tax",$sum_tax);  //总税收
        $this->assign("sum_kj",$sum_kj);  //总开奖金额

        $this->assign("condi",$condi);
        $this->assign("lists",$lists);
        $this->assign("list",$list);
        $this->assign("ct",$tinfo);
        $this->display('game_brnn');
    }


    function runtime($mode = 0){
        static $t;
        if(!$mode){
            $t = microtime();
            return;
        }
        $t1 = microtime();
        list($m0,$s0) = split("  ",$t);
        list($m1,$s1) = split("  ",$t1);

        return sprintf("%.3f ms",($s1+$m1-$s0-$m0)*1000);
    }

    public function get_one($time , $servid){
        $servid = substr($servid,1);
        $where ="";
        if($time){
            // $t1=$time.' 00:00:00"';
            // $t2=$time.' 23:59:59"';
            // $where .= ' time_virtual>= "'.$t1;
            // $where .= ' and time_virtual<= "'.$t2;
            $where .= '  (time_virtual between "'.$time.' 00:00:00"  and  "'.$time.' 23:59:59") ';
        }

        $res=$this->gamelog("SELECT serverid_virtual,time_virtual,SUM(JSON_EXTRACT(log, '$.caculScore')) as amount,
                            SUM(JSON_EXTRACT(log, '$.betScore')) as bet,
                            SUM(JSON_EXTRACT(log, '$.tax')) as tax  FROM game_player  
                            where $where AND serverid_virtual in ($servid)  GROUP BY serverid_virtual");
        return $res;

    }

    //ajax分条查询
    public function get_one_info(){
        $seid= $_GET['severid'];
        $time= $_GET['time'];

        if($time){
              // $t1=$time.' 00:00:00"';
              // $t2=$time.' 23:59:59"';
              // $where .= ' time_virtual>= "'.$t1;
              // $where .= ' and time_virtual<= "'.$t2;
              $where .= '  (time_virtual between "'.$time.' 00:00:00"  and  "'.$time.' 23:59:59") ';
        } 
        $res=$this->gamelog("SELECT serverid_virtual,time_virtual,SUM(JSON_EXTRACT(log, '$.caculScore')) as amount,
                            SUM(JSON_EXTRACT(log, '$.betScore')) as bet,
                            SUM(JSON_EXTRACT(log, '$.tax')) as tax  FROM game_player  
                            where $where AND serverid_virtual=".(int)$seid."  GROUP BY serverid_virtual");
        $result['amount']=0;
        $result['tax']=0;
        $result['servid'] = $_GET['severid'];
        $result['kj']=0;
        $result['bet']=0;
        if($res){
            foreach ($res as $key => $value) {
                $result['amount']+=(-$value['amount']);   ////盈亏
                $result['tax']+=$value['tax'];     //税收
                // if(is_object($value['bet'])){
                //      $b=get_object_vars($value['bet'])['value'] ;
                //      $result['bet']=$b;    //总下注金额
                // }else{
                     $result['bet']+=$value['bet'];    //总下注金额
                // }
                $result['kj']+=($value['bet']+$value['amount']);  ///开奖
                $result['tax']+=$value['tax'];  //税
            }
        }
        $bet=$result['bet'];
        $tax=$result['tax'];
        $amount=$result['amount'];
        $mysql=$this->weblog("UPDATE game_statistics set bet=$bet,tax=$tax,amount=$amount,status=1 WHERE severID=$seid and time='$time'");
         $this->ajaxReturn($result);
    }
}